﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

using VK.Comm;
using VK.Comm.TransactSql;

namespace VK.Casemeet.Slides
{
    internal static class SlideDAL
    {
        internal static SlideEntity GetSlideByReader(IDataReader dr)
        {
            SlideEntity slide = new SlideEntity();

            slide.SlideID = dr.IsDBNull(0) ? 0 : dr.GetInt32(0);
            slide.Explain = dr.IsDBNull(1) ? null : dr.GetString(1);
            slide.Width = dr.IsDBNull(2) ? 0 : dr.GetInt32(2);
            slide.Height = dr.IsDBNull(3) ? 0 : dr.GetInt32(3);
            slide.Created = Util.ConverDateTime(dr.GetValue(4));
            slide.ProvinceID = dr.IsDBNull(5) ? 0 : dr.GetInt32(5);
            slide.CityID = dr.IsDBNull(6) ? 0 : dr.GetInt32(6);
            slide.ProvinceName = dr.IsDBNull(7) ? "全部" : dr.GetString(7);
            slide.CityName = dr.IsDBNull(8) ? "全部" : dr.GetString(8);

            return slide;
        }

        internal static int GetSlideCount(IList<Condition> conditionArray)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int result = 0;

            string SelectSlide = @"SELECT COUNT([Slide].[SlideID]) FROM [Slide] 
                        LEFT OUTER JOIN Area AS AreaProvince ON [Slide].ProvinceID = AreaProvince.AreaID 
                        LEFT OUTER JOIN Area AS AreaCity ON [Slide].CityID = AreaCity.AreaID";

            List<DbParameter> parameterlst = new List<DbParameter>();
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectSlide, ref parameterlst);
            DbCommand SelectSlideCommand = db.GetSqlStringCommand(SelectSlide);
            DbHelper.PackDbCommandByDbParameters(SelectSlideCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(SelectSlideCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static List<SlideEntity> GetSlideList(int startRecordIndex, int endRecordIndex, IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<SlideEntity> slidelst = new List<SlideEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectSlide = @"
                    SELECT [SlideID],[Explain],[Width],[Height],[Created],[ProvinceID],[CityID],[ProvinceName],[CityName]
                    FROM (
                        SELECT [Slide].[SlideID],[Slide].[Explain],[Slide].[Width],[Slide].[Height],[Slide].[Created],
                            [Slide].[ProvinceID],[Slide].[CityID],[AreaProvince].[Name] AS ProvinceName,
                            [AreaCity].[Name] AS CityName, ROW_NUMBER() OVER (ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectSlide += " " + sortField + " " + sortMode + ",";
            SelectSlide += @" [Slide].[SlideID] Desc
                    ) AS rowNum 
                        FROM [Slide] 
                            LEFT OUTER JOIN Area AS AreaProvince ON [Slide].ProvinceID = AreaProvince.AreaID 
                            LEFT OUTER JOIN Area AS AreaCity ON [Slide].CityID = AreaCity.AreaID ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectSlide, ref parameterlst);
            SelectSlide += @"
                    ) AS [Slide] 
                    WHERE rowNum BETWEEN @StartRecordIndex and @EndRecordIndex ";

            DbCommand SelectSlideCommand = db.GetSqlStringCommand(SelectSlide);
            parameterlst.Add(DbHelper.PackInParameter(db, "@StartRecordIndex", DbType.Int32, startRecordIndex));
            parameterlst.Add(DbHelper.PackInParameter(db, "@EndRecordIndex", DbType.Int32, endRecordIndex));
            DbHelper.PackDbCommandByDbParameters(SelectSlideCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectSlideCommand))
            {
                while (dr.Read())
                {
                    slidelst.Add(GetSlideByReader(dr));
                }
            }

            return slidelst;
        }

        internal static List<SlideEntity> GetSlideList(IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<SlideEntity> slidelst = new List<SlideEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectSlide = @"
                    SELECT [Slide].[SlideID],[Slide].[Explain],[Slide].[Width],[Slide].[Height],[Slide].[Created],
                            [Slide].[ProvinceID],[Slide].[CityID],[AreaProvince].[Name] AS ProvinceName,
                            [AreaCity].[Name] AS CityName 
                    FROM [Slide] 
                        LEFT OUTER JOIN Area AS AreaProvince ON [Slide].ProvinceID = AreaProvince.AreaID 
                        LEFT OUTER JOIN Area AS AreaCity ON [Slide].CityID = AreaCity.AreaID";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectSlide, ref parameterlst);
            SelectSlide += " ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectSlide += " " + sortField + " " + sortMode + ",";
            SelectSlide += " [Slide].[SlideID] Desc ";

            DbCommand SelectSlideCommand = db.GetSqlStringCommand(SelectSlide);
            DbHelper.PackDbCommandByDbParameters(SelectSlideCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectSlideCommand))
            {
                while (dr.Read())
                {
                    slidelst.Add(GetSlideByReader(dr));
                }
            }

            return slidelst;
        }

        internal static SlideEntity GetSlide(int slideID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            SlideEntity slide = null;

            string SelectSlide = @"
                    SELECT [Slide].[SlideID],[Slide].[Explain],[Slide].[Width],[Slide].[Height],[Slide].[Created],
                            [Slide].[ProvinceID],[Slide].[CityID],[AreaProvince].[Name] AS ProvinceName,
                            [AreaCity].[Name] AS CityName 
                    FROM [Slide] LEFT OUTER JOIN Area AS AreaProvince ON [Slide].ProvinceID = AreaProvince.AreaID 
                            LEFT OUTER JOIN Area AS AreaCity ON [Slide].CityID = AreaCity.AreaID
                    WHERE [Slide].[SlideID]=@SlideID
                ";

            DbCommand SelectSlideCommand = db.GetSqlStringCommand(SelectSlide);
            db.AddInParameter(SelectSlideCommand, "@SlideID", DbType.Int32, slideID);

            using (IDataReader dr = db.ExecuteReader(SelectSlideCommand))
            {
                if (dr.Read())
                {
                    slide = GetSlideByReader(dr);
                }
            }

            return slide;
        }

        internal static int InsertSlide(SlideEntity slide)
        {
            Database db = DatabaseFactory.CreateDatabase();

            StringBuilder InsertSlide = new StringBuilder();
            List<DbParameter> parameterlst = new List<DbParameter>();

            InsertSlide.Append(@"Declare @SlideID int;
                    INSERT INTO [Slide] 
				([Explain],[Width],[Height],[Created],[ProvinceID],[CityID])
				 VALUES (@Explain,@Width,@Height,@Created,@ProvinceID,@CityID);
                    SET @SlideID=@@IDENTITY;
                ");

            parameterlst.Add(DbHelper.PackInParameter(db, "@Explain", DbType.String, slide.Explain));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Width", DbType.Int32, slide.Width));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Height", DbType.Int32, slide.Height));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Created", DbType.DateTime, slide.Created));
            parameterlst.Add(DbHelper.PackInParameter(db, "@ProvinceID", DbType.Int32, slide.ProvinceID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@CityID", DbType.Int32, slide.CityID));

            #region 插入图片
            string idn = string.Empty;
            for (int n = 0; n < slide.ImgUrllst.Count; n++)
            {
                idn = "_Photo" + n;
                InsertSlide.Append("INSERT INTO [Photo] ([ObjectID],[ObjectType],[ImgUrl]) VALUES (@SlideID, 4, @ImgUrl").Append(idn).Append(");");
                parameterlst.Add(DbHelper.PackInParameter(db, "@ImgUrl" + idn, DbType.String, slide.ImgUrllst[n]));
            }
            #endregion

            InsertSlide.Append("SELECT @SlideID;");

            DbCommand InsertSlideCommand = db.GetSqlStringCommand(WrapSql.WrapTransaction("Tran_InsertSlide", InsertSlide.ToString()));
            DbHelper.PackDbCommandByDbParameters(InsertSlideCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(InsertSlideCommand);

            return obj == null ? 0 : (int)obj;
        }

        internal static int UpdateSlide(SlideEntity slide)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            StringBuilder UpdateSlide = new StringBuilder();
            List<DbParameter> parameterlst = new List<DbParameter>();

            UpdateSlide.Append(@"UPDATE [Slide] 
				 SET [Explain]=@Explain,[Width]=@Width,[Height]=@Height,[Created]=@Created,[ProvinceID]=@ProvinceID,[CityID]=@CityID 
				 WHERE [SlideID] = @SlideID;");

            parameterlst.Add(DbHelper.PackInParameter(db, "@SlideID", DbType.Int32, slide.SlideID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Explain", DbType.String, slide.Explain));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Width", DbType.Int32, slide.Width));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Height", DbType.Int32, slide.Height));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Created", DbType.DateTime, slide.Created));
            parameterlst.Add(DbHelper.PackInParameter(db, "@ProvinceID", DbType.Int32, slide.ProvinceID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@CityID", DbType.Int32, slide.CityID));

            #region 更新图片
            string idn = string.Empty;
            UpdateSlide.Append("DELETE FROM [Photo] WHERE [ObjectID]=@SlideID AND [ObjectType]=4;");
            for (int n = 0; n < slide.ImgUrllst.Count; n++)
            {
                idn = "_Photo" + n;
                UpdateSlide.Append("INSERT INTO [Photo] ([ObjectID],[ObjectType],[ImgUrl],[JumpUrl],[Target]) VALUES (@SlideID, 4, @ImgUrl").Append(idn).Append(", @JumpUrl").Append(idn).Append(", @Target").Append(idn).Append(");");
                parameterlst.Add(DbHelper.PackInParameter(db, "@ImgUrl" + idn, DbType.String, slide.ImgUrllst[n].ImgUrl));
                parameterlst.Add(DbHelper.PackInParameter(db, "@JumpUrl" + idn, DbType.String, slide.ImgUrllst[n].JumpUrl));
                parameterlst.Add(DbHelper.PackInParameter(db, "@Target" + idn, DbType.String, slide.ImgUrllst[n].Target));
            }
            #endregion

            DbCommand UpdateSlideCommand = db.GetSqlStringCommand(WrapSql.WrapTransaction("Tran_UpdateSlide", UpdateSlide.ToString()));
            DbHelper.PackDbCommandByDbParameters(UpdateSlideCommand, parameterlst.ToArray());

            result = db.ExecuteNonQuery(UpdateSlideCommand);

            return result;
        }

        internal static int DeleteSlide(int slideID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string DeleteSlide = @"
                DELETE FROM [Slide] WHERE SlideID=@SlideID;
                DELETE FROM [Photo] WHERE ObjectID=@SlideID AND ObjectType=4;";

            DbCommand DeleteSlideCommand = db.GetSqlStringCommand(DeleteSlide);
            db.AddInParameter(DeleteSlideCommand, "@SlideID", DbType.Int32, slideID);
            result = db.ExecuteNonQuery(DeleteSlideCommand);

            return result;
        }

        internal static int SetSlideEnable(int slideID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetSlide = @"UPDATE [Slide]
                    SET [IsEnable] = ([IsEnable]+1)%2
                    WHERE [SlideID] = @SlideID";

            DbCommand SetSlideCommand = db.GetSqlStringCommand(SetSlide);
            db.AddInParameter(SetSlideCommand, "@SlideID", DbType.Int32, slideID);
            result = db.ExecuteNonQuery(SetSlideCommand);

            return result;
        }

        internal static int HitSlide(int slideID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetSlide = @"UPDATE [Slide]
                    SET [Hits] = [Hits]+1
                    WHERE [SlideID] = @SlideID";

            DbCommand SetSlideCommand = db.GetSqlStringCommand(SetSlide);
            db.AddInParameter(SetSlideCommand, "@SlideID", DbType.Int32, slideID);
            result = db.ExecuteNonQuery(SetSlideCommand);

            return result;
        }
    }
}
